package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.RoadpoleDto;
import com.hualude.roadsign.dto.MapInfoDto;
import com.hualude.roadsign.model.RoadpoleEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

/**
 * Created by shao-yikun on 2017/4/7.
 */
public interface RoadPoleRepository extends CrudRepository<RoadpoleEntity, Long> {

    /**
     * @Description: 标杆信息的DT展示
     * @Author: shao-yikun
     * @Date: 2017/3/4 18:59
     */
    public List<RoadpoleDto> getRoadPoleCollect4DT(DTRequestDto dtRequestDto, Integer orgId, String actorIds,
                                                   String startRoad, String endRoad, String roadName, String saveState) throws Exception;

    /**
     * @Description: 标杆信息的DT展示的数量
     * @Author: shao-yikun
     * @Date: 2017/3/4 18:59
     */
    public Integer getCount4DT(Integer orgId, String actorIds, String startRoad, String endRoad,
                               String roadName, String saveState, String projectNumber) throws Exception;

    /**
     * @Description: 补充或修改标杆信息
     * @Author: shao-yikun
     * @Date: 2017/3/5 14:08
     */
    @Modifying
    @Query(value = "update RoadpoleEntity set area = ?2, " +
            "longitude = ?3, latitude = ?4, roadName = ?5, startRoad = ?6, endRoad = ?7, " +
            "mainOrgId = ?8, roadPoleSecurity = ?9, roadPoleBridge = ?10, roadPoleShape = ?11, " +
            "roadPoleSize = ?12, roadPolePic = ?13, roadPoleRegionId = ?14, projectNumber = ?15, " +
            "modifierId = ?16, roadPoleIsComplete = 0 where roadPoleId = ?1")
    public Integer updateRoadPoleCollect(Integer roadPoleId,
                                         String area,
                                         String longitude,
                                         String latitude,
                                         String roadName,
                                         String startRoad,
                                         String endRoad,
                                         Integer mainOrgId,
                                         Integer roadPoleSecurity,
                                         Integer roadPoleBridge,
                                         Integer roadPoleShape,
                                         String roadPoleSize,
                                         String roadPolePic,
                                         Integer roadPoleRegionId,
                                         String projectNumber,
                                         Integer modifierId) throws Exception;

    /**
     * @Description: 删除标杆
     * @Author: shao-yikun
     * @Date: 2017/3/5 15:16
     */
    @Modifying
    @Query(value = "delete from RoadpoleEntity where roadPoleId = ?1")
    public Integer deleteRoadPoleCollect(Integer roadPoleId) throws Exception;

    /**
     * Description 标杆DT4标牌审核
     *
     * @Author jiang-weirong
     * @Date 2017/4/10 13:10
     */
    public List<RoadpoleDto> getRoadPoleAudit4DT(DTRequestDto dtRequestDto, Integer orgId, String actorIds) throws Exception;

    public Integer getCount4RoadPoleAuditDT(String search, Integer orgId, String actorIds) throws Exception;

    /**
     * Description 标杆审核
     *
     * @Author jiang-weirong
     * @Date 2017/4/10 15:21
     */
    @Modifying
    @Query(value = "update RoadpoleEntity set  roadPoleIsComplete = ?2 where roadPoleId=?1")
    public Integer auditRoadPole(Integer roadPoleIds, Integer flag) throws Exception;

    @Query(value = "from RoadpoleEntity where roadPoleId=?1")
    public RoadpoleEntity getRoadPoleByRoadPoleId(Integer roadPoleId) throws Exception;

    /**
     * @Description: 得到标杆坐标根据公司Id
     * @Author: shao-yikun
     * @Date: 2017/3/14 22:46
     */
    public List<RoadpoleDto> getCoordinateByOrgId(Integer orgId,
                                                  String actorIds,
                                                  double neLongitude,
                                                  double neLatitude,
                                                  double swLongitude,
                                                  double swLatitude) throws Exception;

    /**
     * @Description: 获取标杆坐标用于地图显示
     * @Author: xu-jiaHui
     * @Date: 2017/4/22 13:39
     */
    public List<RoadpoleDto> getRoadPoleCoordinate4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception;


    /**
     * @Description: 根据坐标获取标杆信息
     * @Author: xu-jiaHui
     * @Date: 2017/4/22 15:53
     */

    public List<RoadpoleDto> getRoadPoleByXY(String longitude, String latitude) throws Exception;

    /**
     * @Description: 通过路名获取路段
     * @Author: xu-jiaHui
     * @Date: 2017/4/23 22:43
     */
    public List<String> getRoadByRoadName(String roadName, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 通过起始路段搜索截止路段
     * @Author: xu-jiaHui
     * @Date: 2017/4/23 23:50
     */
    public List<String> getSectionByRoadName(String roadName, Integer actorId, Integer orgId) throws Exception;


    /**
     * @Description: 更新标杆完整性状态
     * @Author: shao-yikun
     * @Date: 2017/4/25 12:44
     */
    @Modifying
    @Query(value = "update RoadpoleEntity set modifierId = ?2, roadPoleIsComplete = 0 " +
            "where roadPoleId = ?1")
    public Integer setRoadPoleIsComplete(Integer roadPoleId, Integer userId) throws Exception;

    /**
     * @Description: app端标杆搜索
     * @author chenzetong
     * @date 2017/4/24 20:12
     */
    public List<RoadpoleDto> getRoadPoleBySearch(Integer orgId, String actorIds, String strSearch, Integer mCount, Integer mySize) throws Exception;


    public List<RoadpoleDto> getRoadPole2Report4DT(DTRequestDto dtRequestDto, Integer orgId, Integer actorId, String startRoad, String endRoad) throws Exception;

    public Integer getRoadPole2ReportCount4DT(String strSearch, Integer orgId, Integer actorId) throws Exception;


    /**
     * @Description: 批量设置标杆的监管单位
     * @Author: xu-jiaHui
     * @Date: 2017/5/4 21:03
     */
    @Modifying
    @Query(value = "update RoadpoleEntity set mainOrgId=?2 where roadPoleId=?1")
    public Integer setRoadPoleMainOrg(Integer roadPoleId, Integer mainOrgId) throws Exception;

    /**
     * @Description: 故障上报后更新标杆安全状态为不安全
     * @Author: xu-jiaHui
     * @Date: 2017/5/6 23:16
     */
    @Modifying
    @Query(value = "update RoadpoleEntity set roadPoleSecurity=1 where roadPoleId=?1")
    public Integer updateRoadPoleSecurity(Integer roadPoleId) throws Exception;

    @Modifying
    @Query(value = "update RoadpoleEntity rp set rp.roadPoleSecurity=1 where rp.roadPoleId in (select rs.roadPoleId from RoadsignEntity rs where rs.roadSignId=?1 )")
    public Integer updateRoadPoleSecurityBySignId(Integer roadSignId) throws Exception;

    /**
     * @Description: 完成维修后更新状态为正常
     * @Author: shao-yikun
     * @Date: 2017/5/6 15:07
     */
    @Modifying
    @Query(value = "update RoadpoleEntity set roadPoleSecurity = 0 where roadPoleId = ?1")
    public void updateRoadPoleState(Integer roadPoleId) throws Exception;

    /**
     * @Description: 通过公司Id获取所有路名
     * @author chenzetong
     * @date 2017/5/18 13:42
     */
    @Query(value = "select distinct roadName from RoadpoleEntity where orgId = ?1")
    public List<String> getRoadByOrgId(Integer orgId) throws Exception;

    @Query(value = "select distinct roadName from RoadpoleEntity where orgId = ?1 and city = ?2")
    public List<String> getRoadByOrgId2(Integer orgId, String city) throws Exception;

    /**
     * @Description: 通过公司获取所有城市
     * @Author: shao-yikun
     * @Date: 2017/6/1 下午4:52
     */
    @Query(value = "select distinct city from RoadpoleEntity where orgId = ?1")
    public List<String> getCityByOrgId(Integer orgId) throws Exception;

    /**
     * @Description: app端标杆搜索(路名检索)
     * @author chenzetong
     * @date 2017/4/24 20:12
     */
    public List<RoadpoleDto> getRoadPoleByFiltrate(String roadName, String city, Integer orgId, String actorIds) throws Exception;

    /**
     * @Description: 获取标杆datatable4申请故障
     * @Author: pan-jianan
     * @Date: 2017/5/19 0:26
     */
    public List<RoadpoleDto> getRoadPole4ReportDT(DTRequestDto dtRequestDto, Integer orgId, String actorIds,
                                                  String startRoad, String endRoad, String roadName, String isSave) throws Exception;

    /**
     * @Description: 获取标杆datatable4申请故障count
     * @Author: pan-jianan
     * @Date: 2017/5/19 0:26
     */
    public Integer getRoadPole4ReportCountDT(Integer orgId, String actorIds, String startRoad, String endRoad, String roadName, String isSave) throws Exception;

    /**
     * @Description: 通过坐标获取标杆信息
     * @author xu_jiaHui
     * @date 2017/7/29 16:14
     */
    @Query(value = "select new com.hualude.roadsign.dto.MapInfoDto(rp.roadPoleId,rp.roadPolePic,rp.projectNumber,rp.province,rp.city," +
            "rp.area,rp.roadName,rp.startRoad,rp.endRoad,rp.roadPoleSize,rp.roadPoleBridge,rp.roadPoleShape,u.userName," +
            "o1.orgName,o2.orgName) from RoadpoleEntity rp,OrgEntity o1,OrgEntity o2,UserEntity u where rp.orgId=o1.orgId" +
            " and rp.mainOrgId=o2.orgId and rp.createUserId=u.userId and rp.longitude=?1 and rp.latitude=?2")
    public List<MapInfoDto> getRoadPoleInfoByXY(String solarEnergyLongitude, String solarEnergyLatitude) throws Exception;

}
